package vip.xiaonuo.smzq.modular.nbfb.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import vip.xiaonuo.smzq.modular.hisi.entity.vo.KcdgmtjVo;
import vip.xiaonuo.smzq.modular.kcl.entity.Zycl;

import java.util.List;
import java.util.Map;

@Mapper
public interface ZyclSixMapper extends BaseMapper<Zycl> {


    @Select("select kcmc,ksldw from kcl_cb06_kc  where kcdm = #{kcdm}")
    Map<String, String> getKcmc(Integer kcdm);
    @Select("select kcmc,jsdxmc,jsldw from kcl_cb06_kc where kcdm = #{kcdm} and tjdx = #{tjdx}")
    Map<String, String> getKcmcTjdx(@Param("kcdm") Integer kcdm,@Param("tjdx") Integer tjdx);

    /**
     * 市查县以及县自查
     * @param nd
     * @return
     */
    @Select("with tj as (\n" +
            "\tselect k\n" +
            "\t\t.kqbh,\n" +
            "\t\tzy.kcdm,\n" +
            "\t\tzy.tjdx,\n" +
            "\t\tk.xzqdm,\n" +
            "\t\tcoalesce (\n" +
            "\t\t\tsum (\n" +
            "\t\t\tcase\n" +
            "\t\t\t\t\t\n" +
            "\t\t\t\t\twhen ( zy.zycllb = 19002 and c.gmbs = 0 ) then\n" +
            "\t\t\t\t\tzy.jsl \n" +
            "\t\t\t\t\twhen ( zy.zycllb = 19002 and c.gmbs = 1 ) then\n" +
            "\t\t\t\t\tzy.ksl else 0 \n" +
            "\t\t\t\tend \n" +
            "\t\t\t\t),\n" +
            "\t\t\t\t0 \n" +
            "\t\t\t) as total_sum,\n" +
            "\t\t\tcoalesce (\n" +
            "\t\t\t\tsum ( case when ( zy.zycllb = 19001  ) then zy.jsl else 0 end ),\n" +
            "\t\t\t\t0 \n" +
            "\t\t\t) as by_jsl,\n" +
            "\t\t\tcoalesce (\n" +
            "\t\t\t\tSUM ( CASE WHEN ( zy.zycllb = 19001 ) THEN zy.ksl ELSE 0 END ),\n" +
            "\t\t\t\t0 \n" +
            "\t\t\t) AS by_ksl \n" +
            "\t\tFROM\n" +
            "\t\t\tkcl_ks\n" +
            "\t\t\tK LEFT JOIN kcl_zycl zy ON ( zy.nd = K.nd AND zy.kqbh = K.kqbh AND zy.djflbh = K.djflbh )\n" +
            "\t\t\tLEFT JOIN kcl_cb06_kc C ON ( zy.kcdm = C.kcdm AND C.tjdx = zy.tjdx ) \n" +
            "\t\tWHERE\n" +
            "\t\t\tK.sbbs <> 3 \n" +
            "\t\t\tAND zy.zycllx IN ( 'TM', 'KZ', 'TD' ) \n" +
            "\t\t\tAND CAST ( K.xzqdm AS VARCHAR ) LIKE'13%' \n" +
            "\t\t\tAND K.nd = #{nd}\n" +
            "\t\tGROUP BY\n" +
            "\t\t\tK.kqbh,\n" +
            "\t\t\tzy.kcdm,\n" +
            "\t\t\tzy.tjdx, \n" +
            "\t\t\tk.xzqdm\n" +
            "\t\t) \n" +
            "\t\t\n" +
            "\t\tSELECT \n" +
            "\t\tT.kcdm,\n" +
            "\t\tMC.kcmc,\n" +
            "\t\tT.tjdx,\n" +
            "\t\tMC.jsdxmc,\n" +
            "\t\tgmtype,\n" +
            "\t\tT.xzqdm,\n" +
            "\t\tCOUNT ( 1 ) AS gmcount,\n" +
            "\t\tCOALESCE ( to_number( to_char( SUM ( total_sum ), '9999999999.99' ), '9999999999.99' ), 0 ) AS ljl,\n" +
            "\t\tCOALESCE ( to_number( to_char( SUM ( by_jsl ), '9999999999.99' ), '9999999999.99' ), 0 ) AS byjsl,\n" +
            "\t\tCOALESCE ( to_number( to_char( SUM ( by_ksl ), '9999999999.99' ), '9999999999.99' ), 0 ) AS byksl,\n" +
            "\t\tCOALESCE (to_number( to_char( SUM (case when MC.xsbs = 0 then by_jsl else by_ksl end), '9999999999.99' ), '9999999999.99' ), 0 ) byl \n" +
            "\tFROM\n" +
            "\t\t(\n" +
            "\t\tSELECT\n" +
            "\t\t\tTJ.*,\n" +
            "\t\tCASE\n" +
            "\t\t\t\tWHEN ( TJ.total_sum / BZ.dwhs ) BETWEEN 0 \n" +
            "\t\t\t\tAND BZ.zxxx THEN\n" +
            "\t\t\t\t\t'小型' \n" +
            "\t\t\t\t\tWHEN ( TJ.total_sum / BZ.dwhs ) BETWEEN BZ.zxxx \n" +
            "\t\t\t\t\tAND BZ.dxxx THEN\n" +
            "\t\t\t\t\t\t'中型' ELSE'大型' \n" +
            "\t\t\t\t\t\tEND AS gmtype \n" +
            "\t\t\t\tFROM\n" +
            "\t\t\t\t\tTJ\n" +
            "\t\t\t\t\tLEFT JOIN kcl_cb15_kczyclgmdz BZ ON TJ.kcdm = BZ.kcdm \n" +
            "\t\t\t\t\tAND TJ.tjdx = BZ.tjdx \n" +
            "\t\t\t\t)\n" +
            "\t\t\t\tT LEFT JOIN kcl_cb06_kc MC ON T.kcdm = MC.kcdm \n" +
            "\t\t\t\tAND T.tjdx = MC.tjdx \n" +
            "\t\t\tGROUP BY\n" +
            "\t\t\t\tT.kcdm,\n" +
            "\t\t\t\tMC.kcmc,\n" +
            "\t\t\t\tT.tjdx,\n" +
            "\t\t\tMC.jsdxmc,\n" +
            "\t\t\tT.xzqdm,\n" +
            "\tgmtype;\n" +
            "\t")
    List<Map> getGm1(Integer nd);

    @Select("WITH tj AS (\n" +
            "    SELECT\n" +
            "        k.kqbh,\n" +
            "        k.kcdm,\n" +
            "        k.tjdx,\n" +
            "        k.xzqdm,\n" +
            "        COALESCE(SUM(CASE\n" +
            "            WHEN c.gmbs = 0 THEN zy.ljcmjsl\n" +
            "            WHEN c.gmbs = 1 THEN zy.ljcmksl\n" +
            "            ELSE 0\n" +
            "        END), 0) AS total_sum,\n" +
            "        COALESCE(SUM(CASE\n" +
            "            WHEN c.gmbs = 0 THEN zy.nmbyjsl\n" +
            "            ELSE 0\n" +
            "        END), 0) AS by_jsl,\n" +
            "        COALESCE(SUM(CASE\n" +
            "            WHEN c.gmbs = 1 THEN zy.nmbyksl\n" +
            "            ELSE 0\n" +
            "        END), 0) AS by_ksl\n" +
            "    FROM hisi_kshz k\n" +
            "    LEFT JOIN hisi_ks_zyclhz zy ON (zy.nd = k.nd AND zy.kqbh = k.kqbh AND zy.djflbh = k.djflbh AND k.kcdm = zy.kcdm AND k.tjdx = zy.tjdx)\n" +
            "    LEFT JOIN kcl_cb06_kc c ON (zy.kcdm = c.kcdm AND c.tjdx = zy.tjdx)\n" +
            "    LEFT JOIN kcl_cb08_xzqh xzq ON k.xzqdm = xzq.dm::VARCHAR\n" +
            "    WHERE\n" +
            "        k.sbbsm <> 3\n" +
            "        AND zy.zycllx IN ('TM', 'KZ', 'TD')\n" +
            "        AND CAST(k.xzqdm AS VARCHAR) LIKE '13%'\n" +
            "        AND k.nd = #{nd}\n" +
            "    GROUP BY\n" +
            "        k.kqbh,\n" +
            "        k.kcdm,\n" +
            "        k.tjdx,\n" +
            "       k.xzqdm\n" +
            "),\n" +
            "\n" +
            "-- 计算 gmtype\n" +
            "gm_type AS (\n" +
            "    SELECT\n" +
            "        tj.*,\n" +
            "        CASE\n" +
            "            WHEN (tj.total_sum / bz.dwhs) BETWEEN 0 AND bz.zxxx THEN '小型'\n" +
            "            WHEN (tj.total_sum / bz.dwhs) BETWEEN bz.zxxx AND bz.dxxx THEN '中型'\n" +
            "            ELSE '大型'\n" +
            "        END AS gmtype\n" +
            "    FROM tj\n" +
            "    LEFT JOIN kcl_cb15_kczyclgmdz bz ON tj.kcdm = bz.kcdm AND tj.tjdx = bz.tjdx\n" +
            ")\n" +
            "\n" +
            "SELECT\n" +
            "    T.kcdm,\n" +
            "    MC.kcmc,\n" +
            "    T.tjdx,\n" +
            "    MC.jsdxmc,\n" +
            "    T.xzqdm,\n" +
            "    T.gmtype,\n" +
            "   \tCOUNT ( 1 ) AS gmcount,\n" +
            "\t\tCOALESCE ( to_number( to_char( SUM ( total_sum ), '9999999999.99' ), '9999999999.99' ), 0 ) AS ljl,\n" +
            "\t\tCOALESCE ( to_number( to_char( SUM ( by_jsl ), '9999999999.99' ), '9999999999.99' ), 0 ) AS byjsl,\n" +
            "\t\tCOALESCE ( to_number( to_char( SUM ( by_ksl ), '9999999999.99' ), '9999999999.99' ), 0 ) AS byksl,\n" +
            "\t\tCOALESCE (to_number( to_char( SUM (case when MC.xsbs = 0 then by_jsl else by_ksl end), '9999999999.99' ), '9999999999.99' ), 0 ) byl \n" +
            "FROM gm_type T\n" +
            "LEFT JOIN kcl_cb06_kc MC ON T.kcdm = MC.kcdm AND T.tjdx = MC.tjdx\n" +
            "GROUP BY\n" +
            "    T.kcdm,\n" +
            "    MC.kcmc,\n" +
            "    T.tjdx,\n" +
            "    MC.jsdxmc,\n" +
            "    T.xzqdm,\n" +
            "    T.gmtype\n" +
            "ORDER BY \n" +
            "     T.xzqdm,\n" +
            " T.gmtype")
    List<Map> getGm(Integer nd);
    /**
     * 省级查询规模统计
     * @param nd
     * @return
     */


     @Select("WITH tj AS (\n" +
             "\tSELECT K\n" +
             "\t\t.kqbh,\n" +
             "\t\tzy.kcdm,\n" +
             "\t\tzy.tjdx,\n" +
             "\t\txzq.fdm AS xzqdm,\n" +
             "\t\tCOALESCE (\n" +
             "\t\t\tSUM (\n" +
             "\t\t\tCASE\n" +
             "\t\t\t\t\t\n" +
             "\t\t\t\t\tWHEN ( zy.zycllb = 19002 AND C.gmbs = 0 ) THEN\n" +
             "\t\t\t\t\tzy.jsl \n" +
             "\t\t\t\t\tWHEN ( zy.zycllb = 19002 AND C.gmbs = 1 ) THEN\n" +
             "\t\t\t\t\tzy.ksl ELSE 0 \n" +
             "\t\t\t\tEND \n" +
             "\t\t\t\t),\n" +
             "\t\t\t\t0 \n" +
             "\t\t\t) AS total_sum,\n" +
             "\t\tCOALESCE ( SUM ( CASE WHEN ( zy.zycllb = 19001 ) THEN zy.jsl ELSE 0 END ), 0 ) AS by_jsl,\n" +
             "\tCOALESCE ( SUM ( CASE WHEN ( zy.zycllb = 19001 ) THEN zy.ksl ELSE 0 END ), 0 ) AS by_ksl \n" +
             "FROM\n" +
             "\tkcl_ks\n" +
             "\tK LEFT JOIN kcl_zycl zy ON ( zy.nd = K.nd AND zy.kqbh = K.kqbh AND zy.djflbh = K.djflbh )\n" +
             "\tLEFT JOIN kcl_cb06_kc C ON ( zy.kcdm = C.kcdm AND C.tjdx = zy.tjdx )\n" +
             "\tLEFT JOIN kcl_cb08_xzqh xzq ON ( xzq.dm = K.xzqdm ) \n" +
             "WHERE\n" +
             "\tK.sbbs <> 3 \n" +
             "\tAND zy.zycllx IN ( 'TM', 'KZ', 'TD' ) \n" +
             "\tAND CAST ( K.xzqdm AS VARCHAR ) LIKE'13%' \n" +
             "\tAND K.nd = #{nd} \n" +
             "GROUP BY\n" +
             "\tK.kqbh,\n" +
             "\tzy.kcdm,\n" +
             "\tzy.tjdx,\n" +
             "\txzq.fdm \n" +
             "\t) SELECT T\n" +
             "\t.kcdm,\n" +
             "\tMC.kcmc,\n" +
             "\tT.tjdx,\n" +
             "\tMC.jsdxmc,\n" +
             "\tgmtype,\n" +
             "\tT.xzqdm,\n" +
             "\tCOUNT ( 1 ) AS gmcount,\n" +
             "\tCOALESCE ( to_number( to_char( SUM ( total_sum ), '9999999999.99' ), '9999999999.99' ), 0 ) AS ljl,\n" +
             "\tCOALESCE ( to_number( to_char( SUM ( by_jsl ), '9999999999.99' ), '9999999999.99' ), 0 ) AS byjsl,\n" +
             "\tCOALESCE ( to_number( to_char( SUM ( by_ksl ), '9999999999.99' ), '9999999999.99' ), 0 ) AS byksl,\n" +
             "\tCOALESCE (\n" +
             "\tto_number( to_char( SUM ( CASE WHEN MC.xsbs = 0 THEN by_jsl ELSE by_ksl END ), '9999999999.99' ), '9999999999.99' ),\n" +
             "\t0 \n" +
             "\t) byl \n" +
             "FROM\n" +
             "\t(\n" +
             "\tSELECT\n" +
             "\t\tTJ.*,\n" +
             "\tCASE\n" +
             "\t\t\t\n" +
             "\t\t\tWHEN ( TJ.total_sum / BZ.dwhs ) BETWEEN 0 \n" +
             "\t\t\tAND BZ.zxxx THEN\n" +
             "\t\t\t\t'小型' \n" +
             "\t\t\t\tWHEN ( TJ.total_sum / BZ.dwhs ) BETWEEN BZ.zxxx \n" +
             "\t\t\t\tAND BZ.dxxx THEN\n" +
             "\t\t\t\t\t'中型' ELSE'大型' \n" +
             "\t\t\t\t\tEND AS gmtype \n" +
             "\t\t\tFROM\n" +
             "\t\t\t\tTJ\n" +
             "\t\t\t\tLEFT JOIN kcl_cb15_kczyclgmdz BZ ON TJ.kcdm = BZ.kcdm \n" +
             "\t\t\t\tAND TJ.tjdx = BZ.tjdx \n" +
             "\t\t\t)\n" +
             "\t\t\tT LEFT JOIN kcl_cb06_kc MC ON T.kcdm = MC.kcdm \n" +
             "\t\t\tAND T.tjdx = MC.tjdx \n" +
             "\t\tGROUP BY\n" +
             "\t\t\tT.kcdm,\n" +
             "\t\t\tMC.kcmc,\n" +
             "\t\t\tT.tjdx,\n" +
             "\t\t\tMC.jsdxmc,\n" +
             "\t\tT.xzqdm,\n" +
             "\tgmtype;")
    List<Map> getShiGm1(Integer nd);


     @Select("WITH tj AS (\n" +
             "    SELECT\n" +
             "        k.kqbh,\n" +
             "        k.kcdm,\n" +
             "        k.tjdx,\n" +
             "        xzq.fdm as xzqdm,\n" +
             "        COALESCE(SUM(CASE\n" +
             "            WHEN c.gmbs = 0 THEN zy.ljcmjsl\n" +
             "            WHEN c.gmbs = 1 THEN zy.ljcmksl\n" +
             "            ELSE 0\n" +
             "        END), 0) AS total_sum,\n" +
             "        COALESCE(SUM(CASE\n" +
             "            WHEN c.xsbs = 0 THEN zy.nmbyjsl\n" +
             "            ELSE 0\n" +
             "        END), 0) AS by_jsl,\n" +
             "        COALESCE(SUM(CASE\n" +
             "            WHEN c.xsbs = 1 THEN zy.nmbyksl\n" +
             "            ELSE 0\n" +
             "        END), 0) AS by_ksl\n" +
             "    FROM hisi_kshz k\n" +
             "    LEFT JOIN hisi_ks_zyclhz zy ON (zy.nd = k.nd AND zy.kqbh = k.kqbh AND zy.djflbh = k.djflbh AND k.kcdm = zy.kcdm AND k.tjdx = zy.tjdx)\n" +
             "    LEFT JOIN kcl_cb06_kc c ON (zy.kcdm = c.kcdm AND c.tjdx = zy.tjdx)\n" +
             "    LEFT JOIN kcl_cb08_xzqh xzq ON k.xzqdm = xzq.dm::VARCHAR\n" +
             "    WHERE\n" +
             "        k.sbbsm <> 3\n" +
             "        AND zy.zycllx IN ('TM', 'KZ', 'TD')\n" +
             "        AND CAST(k.xzqdm AS VARCHAR) LIKE '13%'\n" +
             "        AND k.nd =#{nd}\n" +
             "    GROUP BY\n" +
             "        k.kqbh,\n" +
             "        k.kcdm,\n" +
             "        k.tjdx,\n" +
             "        xzq.fdm\n" +
             "),\n" +
             "\n" +
             "-- 计算 gmtype\n" +
             "gm_type AS (\n" +
             "    SELECT\n" +
             "        tj.*,\n" +
             "        CASE\n" +
             "            WHEN (tj.total_sum / bz.dwhs) BETWEEN 0 AND bz.zxxx THEN '小型'\n" +
             "            WHEN (tj.total_sum / bz.dwhs) BETWEEN bz.zxxx AND bz.dxxx THEN '中型'\n" +
             "            ELSE '大型'\n" +
             "        END AS gmtype\n" +
             "    FROM tj\n" +
             "    LEFT JOIN kcl_cb15_kczyclgmdz bz ON tj.kcdm = bz.kcdm AND tj.tjdx = bz.tjdx\n" +
             ")\n" +
             "\n" +
             "SELECT\n" +
             "    T.kcdm,\n" +
             "    MC.kcmc,\n" +
             "    T.tjdx,\n" +
             "    MC.jsdxmc,\n" +
             "    T.xzqdm,\n" +
             "    T.gmtype,\n" +
             "   \tCOUNT ( 1 ) AS gmcount,\n" +
             "\t\tCOALESCE ( to_number( to_char( SUM ( total_sum ), '9999999999.99' ), '9999999999.99' ), 0 ) AS ljl,\n" +
             "\t\tCOALESCE ( to_number( to_char( SUM ( by_jsl ), '9999999999.99' ), '9999999999.99' ), 0 ) AS byjsl,\n" +
             "\t\tCOALESCE ( to_number( to_char( SUM ( by_ksl ), '9999999999.99' ), '9999999999.99' ), 0 ) AS byksl,\n" +
             "\t\tCOALESCE (to_number( to_char( SUM (case when MC.xsbs = 0 then by_jsl else by_ksl end), '9999999999.99' ), '9999999999.99' ), 0 ) byl \n" +
             "FROM gm_type T\n" +
             "LEFT JOIN kcl_cb06_kc MC ON T.kcdm = MC.kcdm AND T.tjdx = MC.tjdx\n" +
             "GROUP BY\n" +
             "    T.kcdm,\n" +
             "    MC.kcmc,\n" +
             "    T.tjdx,\n" +
             "    MC.jsdxmc,\n" +
             "    T.xzqdm,\n" +
             "    T.gmtype\n" +
             "ORDER BY \n" +
             "     T.xzqdm,\n" +
             " T.gmtype")
    List<Map> getShiGm(Integer nd);



     @Select("with base as (\n" +
             "select ks.kqbh, ks.djflbh, ks.xzqdm, ks.gm,\n" +
             " case when  LEFT(ks.kqbh, 4) in ('1324','1326') then '130600' else  LEFT(ks.kqbh, 4) ||'00' end xzqbh,  ks.kcmc, ks.jsdxmc, ks.kcdm, ks.tjdx,zy.zycllx,\n" +
             " case when kc.xsbs = 0 then zy.jsldw else zy.ksldw end dw,\n" +
             " case when kc.xsbs = 0 then nmbyjsl else nmbyksl end nmby\n" +
             "from hisi_kshz ks\n" +
             "left join hisi_ks_zyclhz zy\n" +
             "on ks.nd = zy.nd and ks.kqbh = zy.kqbh and ks.djflbh = zy.djflbh and ks.kcdm = zy.kcdm and ks.tjdx = zy.tjdx\n" +
             "left join kcl_cb06_kc kc on ks.kcdm=kc.kcdm and ks.tjdx = kc.tjdx\n" +
             "where ks.nd = #{nd} and ks.sbbsm<>3 and zy.zycllx IN ('TM', 'KZ', 'TD')\n" +
             "),\n" +
             "\n" +
             "tmp1 as (\n" +
             "select kcmc, jsdxmc, kcdm, tjdx, xzqbh, kqbh, max(dw) dw, max(gm) gm from base \n" +
             "group by kcmc, jsdxmc, kcdm, tjdx, xzqbh, kqbh\n" +
             "),\n" +
             "\n" +
             "head1 as (\n" +
             "select tmp1.xzqbh, xzq.mc, kcmc, case when kcdm in (42201, 42202, 32006, 63701) then '' else jsdxmc end jsdxmc, kcdm, case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end tjdx, max(dw) dw, \n" +
             " sum(case when gm='大型' then 1 else 0 end) dxs,\n" +
             " sum(case when gm='中型' then 1 else 0 end) zxs,\n" +
             " sum(case when gm='小型' then 1 else 0 end) xxs\n" +
             "from tmp1 \n" +
             "left join kcl_cb08_xzqh xzq on tmp1.xzqbh = xzq.dm::varchar\n" +
             "group by kcmc, case when kcdm in (42201, 42202, 32006, 63701) then '' else jsdxmc end, kcdm, case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end, tmp1.xzqbh, xzq.mc\n" +
             "),\n" +
             "\n" +
             "head2 as (\n" +
             "select xzq.fdm, kcmc, jsdxmc, kcdm, tjdx, max(dw) dw, sum(dxs) dxs, sum(zxs) zxs, sum(xxs) xxs\n" +
             "from head1 \n" +
             "left join kcl_cb08_xzqh xzq on head1.xzqbh = xzq.dm::varchar\n" +
             "group by kcmc, jsdxmc, kcdm, tjdx, xzq.fdm\n" +
             "),\n" +
             "\n" +
             "head as (\n" +
             "select head2.fdm::varchar, xzq.mc, kcmc, jsdxmc, kcdm, tjdx, dw, dxs, zxs, xxs \n" +
             "from head2\n" +
             "left join kcl_cb08_xzqh xzq on head2.fdm = xzq.dm\n" +
             "union \n" +
             "select * from head1\n" +
             "),\n" +
             "\n" +
             "\n" +
             "data1 as (\n" +
             "select kcdm, case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end tjdx, xzq.fdm, \n" +
             "       sum(case when gm='大型' then nmby else 0 end) dxl,\n" +
             "       sum(case when gm='中型' then nmby else 0 end) zxl,\n" +
             "       sum(case when gm='小型' then nmby else 0 end) xxl\n" +
             "from base\n" +
             "left join kcl_cb08_xzqh xzq on base.xzqdm = xzq.dm::varchar\n" +
             "group by  kcdm, case when kcdm in (42201, 42202, 32006, 63701) then 0 else tjdx end, xzq.fdm\n" +
             "),\n" +
             "\n" +
             "data2 as(\n" +
             "select kcdm, tjdx, xzq.fdm, sum(dxl) dxl, sum(zxl) zxl, sum(xxl) xxl\n" +
             "from data1\n" +
             "left join kcl_cb08_xzqh xzq on data1.fdm = xzq.dm\n" +
             "group by kcdm, tjdx, xzq.fdm\n" +
             "),\n" +
             "\n" +
             "\n" +
             "alldata as(\n" +
             "select * from data1\n" +
             "UNION\n" +
             "select * from data2\n" +
             ")\n" +
             "\n" +
             "\n" +
             "select head.mc as xzqhmc,(CASE  WHEN head.jsdxmc <> '' THEN CONCAT(head.kcmc, ' (', head.jsdxmc, ')')  ELSE head.kcmc END )AS kcmc, head.dw, head.dxs as bigcount, cast(dxl AS DECIMAL(18,2)) as bignmby, head.zxs as mediumcount, cast(zxl AS DECIMAL(18,2)) as  mediumnmby, head.xxs as smallcount, cast(xxl AS DECIMAL(18,2)) as smallnmby ,\n" +
             "\t(\n" +
             "\tCASE\n" +
             "\t\t\t\t\n" +
             "\t\t\t\tWHEN head.tjdx :: VARCHAR IS NOT NULL \n" +
             "\t\t\t\tAND head.tjdx :: VARCHAR <> '' THEN\n" +
             "\t\t\t\t\tCONCAT ( head.kcdm :: VARCHAR, '_', head.tjdx :: VARCHAR ) ELSE head.kcdm :: VARCHAR \n" +
             "\t\t\t\tEND \n" +
             "\t\t\t\t) AS bz \n" +
             "\n" +
             "from head \n" +
             "left join alldata on head.kcdm=alldata.kcdm and head.tjdx=alldata.tjdx and head.fdm = alldata.fdm::varchar\n" +
             "order by head.kcdm, head.tjdx, head.fdm\n")
     List<KcdgmtjVo> getNbfbsix(Integer nd);
}
